﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class UserDAL : SqlBaseDal
    {
        public EstateDataSet.UserDataTable GetUser(string login, string password)
        {
            var result = new EstateDataSet.UserDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_Authenticate;

                    var parameters = new SqlParameter[2];
                    parameters[0] = new SqlParameter
                                    {
                                        ParameterName = Constants.Sp_Param_Login,
                                        Value = login,
                                        IsNullable = false,
                                        SqlDbType = SqlDbType.VarChar,
                                        Direction = ParameterDirection.Input
                                    };

                    parameters[1] = new SqlParameter
                                    {
                                        ParameterName = Constants.Sp_Param_Password,
                                        Value = password,
                                        IsNullable = false,
                                        SqlDbType = SqlDbType.VarChar,
                                        Direction = ParameterDirection.Input
                                    };

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal void UpdatePassword(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_ChangePassword;

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.UserId;
                    parameters[1] = filterSql.NewPassword;

                    cmd.Parameters.AddRange(parameters);
                    dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal EstateDataSet.UserDataTable GetUserByEmail(string email)
        {

            var result = new EstateDataSet.UserDataTable();
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_GetByEmail;

                    var parameters = new SqlParameter[1];
                    parameters[0] = new SqlParameter
                                   {
                                       ParameterName = Constants.Sp_Param_Email,
                                       Value = email,
                                       IsNullable = false,
                                       SqlDbType = SqlDbType.VarChar,
                                       Direction = ParameterDirection.Input
                                   };

                    cmd.Parameters.AddRange(parameters);
                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
                return result;
            }
        }


        public void CreateMunicipalityUser(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_CreateMunicipality;

                    var parameters = new SqlParameter[12];
                    parameters[0] = filterSql.Name;
                    parameters[1] = filterSql.FirstName;
                    parameters[2] = filterSql.LastName;
                    parameters[3] = filterSql.Phone1;
                    parameters[4] = filterSql.Phone2;
                    parameters[5] = filterSql.Email;
                    parameters[6] = filterSql.PostCity;
                    parameters[7] = filterSql.PostAddress;
                    parameters[8] = filterSql.PostCode;
                    parameters[9] = filterSql.Login;
                    parameters[10] = filterSql.Password;
                    parameters[11] = filterSql.CenterCoordinates;


                    cmd.Parameters.AddRange(parameters);
                    dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public float GetStartFee(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_GetStartFee;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;

                    cmd.Parameters.AddRange(parameters);
                    dbc.Open();
                    return float.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public void UpdateStartFee(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_UpdateStartFee;

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.StartFee;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public DataTable GetMenuStatuses(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_User_GetMenuStatuses;

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.NetworkOperatorId;
                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);

                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal void UpdateArchiveTime(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_UpdateArchiveTime;

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.Days;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal int GetArchiveTime(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_GetArchiveTime;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;

                    cmd.Parameters.AddRange(parameters);
                    dbc.Open();
                    var res = cmd.ExecuteScalar();
                    if (res.Equals(DBNull.Value))
                        return 0;
                    else
                        return int.Parse(res.ToString());
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal DataTable GetArchiveExportGrid(WorkflowFilterSql filterSql)
        {

            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Municipality_GetArchive;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;
                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);

                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }
    }
}
